/**
 * 教师模块数据库操作文件
 */

const { queryFirst, query, pool, queryPage, sequelize } = require('./common');
const { Op } = require('sequelize');

const teachers = sequelize.models.teachers;

/**
 * 校验添加的数据
 * @param {*} param0 查询参数
 * @returns 
 */
const validAdd = ({ work_no, idNo, username }) => teachers.findOne({
    attributes: ['idNo', 'work_no', 'username'],
    where: {
        [Op.or]: {
            idNo,
            work_no,
            username
        }
    }
}); //queryFirst('SELECT idNo,work_no,username FROM teachers WHERE idNo = ? OR work_no = ? OR username = ?', [idNo, work_no, username]);

/**
 * 添加教师
 * @param {*} teacher 要添加的教师信息
 * @returns 
 */
const addTeacher = (teacher) => teachers.create(teacher); //query('INSERT INTO teachers SET ?', teacher);

/**
 * 添加教师角色
 * @param {*} teacherId 教师id
 * @param {*} roleIds 角色id
 */
const addTeacherRoles = async (teacherId, roleIds) => {
    const conn = await pool.getConnection();
    await conn.beginTransaction();
    const items = roleIds.map(item => [item, teacherId]);
    try {
        await conn.query('DELETE FROM teacher_role WHERE teacher_id = ?', [teacherId]);
        if (items.length) {
            await conn.query('INSERT INTO teacher_role(role_id,teacher_id) VALUES ?', [items]);
        }
        await conn.commit();
    } catch (error) {
        console.log(error);
        await conn.rollback();
        throw error;
    }
    finally {
        conn.release();
    }
}

/**
 * 根据用户名获取教师
 * @param {*} username 用户名
 * @returns 
 */
const getTeacherByUserName = (username) => teachers.findOne({ where: { username } }); //queryFirst('SELECT * FROM teachers WHERE username = ?', [username]);

/**
 * 校验权限
 * @param {*} teacherId 教师ID
 * @param {*} path 路径
 * @returns 
 */
const validRights = (teacherId, path) => queryFirst(`SELECT 1 FROM teacher_role t
INNER JOIN role_menu r on t.role_id = r.role_id
INNER JOIN menus m on r.menu_id = m.id
WHERE t.teacher_id = ? AND m.path = ?`, [teacherId, path]);

/**
 * 校验修改教师
 * @param {*} teacher 
 * @returns 
 */
const validUpdateTeacher = ({ id, idNo, work_no }) => teachers.findOne({
    attributes: ['idNo', 'work_no'],
    where: {
        id: {
            [Op.not]: id
        },
        [Op.or]: {
            idNo,
            work_no
        }
    }
});
// {
//     const { id, idNo, work_no } = teacher;
//     return queryFirst('SELECT idNo,work_no FROM teachers WHERE id <> ? AND (idNo = ? OR work_no = ?)', [id, idNo, work_no]);
// }

/**
 * 修改教师
 * @param {*} teacher 
 */
const updateTeacher = (teacher) => teachers.update(teacher, { where: { id: teacher.id } });
// {
//     const { id } = teacher;
//     delete teacher.id;
//     return query('UPDATE teachers SET ? WHERE id = ?', [teacher, id]);
// };

/**
 * 修改密码
 * @param {*} teacher 
 */
const updateTeacherPassword = ({ id, password }) => teachers.update({ password }, { where: { id } }); //query('UPDATE teachers SET `password` = ? WHERE id = ?', [password, id]);

/**
 * 根据教师ID获取教师密码
 * @param {*} id 
 * @returns 
 */
const getTeacherPasswordById = (id) => teachers.findOne({
    attributes: ['password'],
    where: {
        id
    }
}); //queryFirst('SELECT `password` FROM teachers WHERE id = ?', [id]);

/**
 * 根据教师ID获取教师状态
 * @param {*} id 
 * @returns 
 */
const getTeacherStatusById = (id) => queryFirst('SELECT `status` FROM teachers WHERE id = ?', [id]);

/**
 * 变更教师状态
 * @param {*} id 
 * @param {*} status 
 */
const updateStatus = async ({ status, id, date }) =>
// sequelize.transaction(async t => {
//     await teachers.update({ status }, { where: { id }, transaction: t });
// });
{
    const conn = await pool.getConnection();
    try {
        await conn.beginTransaction();
        await Promise.all([conn.query('UPDATE teachers SET `status` = ? WHERE id = ?', [status, id]),
        conn.query('INSERT INTO job_status_history SET ?', { status, teacher_id: id, date })]);
        await conn.commit();
    } catch (error) {
        await conn.rollback();
        throw error;
    } finally {
        conn.release();
    }
}

/**
 * 查询教师列表
 * @param {*} obj 
 */
const searchTeacherList = async ({ pageNo, pageSize, name, idNo, phone, subject }) => teachers.findAndCountAll({
    where: {
        ...(name && { name }),
        ...(idNo && { idNo }),
        ...(phone && { phone }),
        ...((subject * 1) && { subject })
    },
    include: [{
        association: 'p',
    },
    {
        association: 's',
    }],
    order: ['id'],
    limit: pageSize * 1,
    offset: pageSize * (pageNo - 1)
});
//     const conn = await pool.getConnection();
//     try {
//         const where = conn.format(`WHERE (? = '' OR t.name LIKE ?) AND (? = '' OR idNo LIKE ?) AND (? = '' OR phone LIKE ?) AND (? = 0 OR subject = ?)`, [name, `%${name}%`, idNo, `%${idNo}%`, phone, `%${phone}%`, subject, subject]);
//         const [[list], [[count]]] = await Promise.all([conn.query(`SELECT id,name,sex,work_no,salary,position1,position,idNo,phone,address,sub,subject,status,username 
//         FROM (SELECT d.name position1,dd.name sub,t.*,ROW_NUMBER() over(order by id) num FROM teachers t
//         LEFT JOIN dictionary d ON t.position = d.id
//         LEFT JOIN dictionary dd ON t.subject = dd.id
//         ${where}
//         ) tt
//         WHERE tt.num > ? AND tt.num <= ?`, [(pageNo - 1) * pageSize, pageNo * pageSize]),
//         conn.query(`SELECT COUNT(1) count FROM teachers t ${where}`)
//         ]);
//         return [list, count.count];
//     } catch (error) {
//         throw error;
//     } finally {
//         conn.release();
//     }
// }

/**
 * 查询有效的教师列表
 * @param {*} obj 
 */
const searchAvailableTeacherList = async ({ pageNo, pageSize, name = '', idNo = '', phone = '', subject, all }) => {
    const conn = await pool.getConnection();
    try {
        const where = conn.format(`WHERE (${all == 'true' ? '1 = 1' : 'status = 1'}) AND (? = '' OR t.name LIKE ?) AND (? = '' OR idNo LIKE ?) AND (? = '' OR phone LIKE ?) AND (? = 0 OR subject = ?)`, [name, `%${name}%`, idNo, `%${idNo}%`, phone, `%${phone}%`, subject, subject]);
        const [[list], [[count]]] = await Promise.all([conn.query(`SELECT id,name,sex,work_no,salary,position1,position,idNo,phone,address,sub,subject,status,username 
        FROM (SELECT d.name position1,dd.name sub,t.*,ROW_NUMBER() over(order by id) num FROM teachers t
        LEFT JOIN dictionary d ON t.position = d.id
        LEFT JOIN dictionary dd ON t.subject = dd.id
        ${where}
        ) tt
        WHERE tt.num > ? AND tt.num <= ?`, [(pageNo - 1) * pageSize, pageNo * pageSize]),
        conn.query(`SELECT COUNT(1) count FROM teachers t ${where}`)
        ]);
        return [list, count.count];
    } catch (error) {
        throw error;
    } finally {
        conn.release();
    }
}

/**
 * 添加教师紧急联系人
 * @param {*} obj 紧急联系人
 * @returns 
 */
const addEmergencyContact = obj => query('INSERT INTO teacher_emergency_contact SET ?', obj);

/**
 * 修改教师紧急联系人
 * @param {*} obj 紧急联系人
 * @returns 
 */
const updateEmergencyContact = (id, obj) => query('UPDATE teacher_emergency_contact SET ? WHERE id = ?', [obj, id]);

/**
 * 删除教师紧急联系人
 * @param {*} obj 紧急联系人
 * @returns 
 */
const deleteEmergencyContact = id => query('DELETE FROM teacher_emergency_contact WHERE id = ?', [id]);

/**
 * 获取教师紧急联系人列表
 * @param {*} id 教师ID
 * @returns 
 */
const getEmergencyContacts = id => query('SELECT * FROM teacher_emergency_contact WHERE teacher_id = ?', [id]);

/**
 * 获取教师状态
 * @param {*} id 教师id
 * @returns 
 */
const getTeacherStatus = id => queryFirst('SELECT status FROM teachers WHERE id = ?', [id]);

/**
 * 搜索工作状态
 * @param {*} obj 
 */
const searchStatus = obj => {
    const { start_date, end_date, status, teacher_id } = obj;
    const where = format(`WHERE (? = 2 OR j.status = ?) AND (IFNULL(?,0) = 0 OR j.teacher_id = ?) AND (IFNULL(?,'') = '' OR j.date >= ?) AND (IFNULL(?,'') = '' OR j.date < ?)`, [
        status, status, teacher_id, teacher_id, start_date, start_date, end_date, end_date
    ]);
    return queryPage(obj, `SELECT * FROM(
        SELECT j.*,t.name teacher_name,ROW_NUMBER() OVER(ORDER BY date DESC)num FROM job_status_history j
        INNER JOIN teachers t ON j.teacher_id = t.id
        ${where}
        )t WHERE t.num > ? AND t.num <= ?`, `SELECT COUNT(1) count FROM job_status_history j ${where}`);
}

/**
 * 获取无效的教师
 * @param {*} ids 要查询的多个教师ID
 * @returns 
 */
const getUnavailableTeachers = ids => queryFirst(`SELECT name FROM teachers WHERE status = 0 AND id IN (?) LIMIT 1`, [ids]);

module.exports = {
    validAdd,
    addTeacher,
    validRights,
    searchStatus,
    updateStatus,
    updateTeacher,
    addTeacherRoles,
    getTeacherStatus,
    searchTeacherList,
    validUpdateTeacher,
    addEmergencyContact,
    getEmergencyContacts,
    getTeacherStatusById,
    getTeacherByUserName,
    updateTeacherPassword,
    getTeacherPasswordById,
    deleteEmergencyContact,
    updateEmergencyContact,
    getUnavailableTeachers,
    searchAvailableTeacherList,
}